-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tidus
-- Create date: 26/8/2011
-- Description:	Paging
-- =============================================
USE QLNS
GO

/******************************************* TotalPage TABLE **************************************/
IF OBJECT_ID ('sp_TotalPageTbl', 'p') IS NOT NULL
	DROP PROC sp_TotalPageTbl
Go
CREATE PROCEDURE sp_TotalPageTbl
	@TblName varchar(100),
	@Record int,
	@TotalPage int OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		DECLARE @sql varchar(500);
		IF (@TblName = 'vi_Department')
			SET @sql = 'SELECT COUNT(*) AS [TotalRecord] INTO Temp FROM '+@TblName+' WHERE  Company_Id = 2';
		ELSE
			SET @sql = 'SELECT COUNT(*) AS [TotalRecord] INTO Temp FROM '+@TblName;
		EXEC (@sql)
		SELECT @TotalPage = [TotalRecord] FROM Temp
		IF (@TotalPage % @Record = 0) 
			SET @TotalPage = @TotalPage/@Record;
		ELSE 
			SET @TotalPage = @TotalPage/@Record + 1;
		DROP TABLE Temp;
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*
DECLARE @TotalPage int
EXEC sp_TotalPageTbl 'vi_EMSS', 2, @TotalPage OUTPUT
PRINT @TotalPage
*/
/***************************************** END TotalPage TABLE ************************************/

IF OBJECT_ID ('sp_TotalPageEmInDep', 'p') IS NOT NULL
	DROP PROC sp_TotalPageEmInDep
Go
CREATE PROCEDURE sp_TotalPageEmInDep
	@DepId int,
	@Record int,
	@TotalPage int OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		DECLARE @sql varchar(500);
		DECLARE @Id varchar(3);
		SET @Id = CAST(@DepId AS varchar(3));
		SET @sql = 'SELECT COUNT(*) AS [TotalRecord] INTO Temp FROM vi_EMCompany WHERE  Em_Dep = '+ @Id ;
		EXEC (@sql)
		SELECT @TotalPage = [TotalRecord] FROM Temp
		IF (@TotalPage % @Record = 0) 
			SET @TotalPage = @TotalPage/@Record;
		ELSE 
			SET @TotalPage = @TotalPage/@Record + 1;
		DROP TABLE Temp;
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*
DECLARE @TotalPage int
EXEC sp_TotalPageEmInDep 5, 15, @TotalPage OUTPUT
PRINT @TotalPage
*/

IF OBJECT_ID ('sp_TotalPageSSInDep', 'p') IS NOT NULL
	DROP PROC sp_TotalPageSSInDep
Go
CREATE PROCEDURE sp_TotalPageSSInDep
	@MonthYear varchar(8),
	@DepId int,
	@Record int,
	@TotalPage int OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		DECLARE @sql varchar(500);
		DECLARE @Id varchar(3);
		SET @Id = CAST(@DepId AS varchar(3));
		SET @sql = 'SELECT COUNT(*) AS [TotalRecord] INTO Temp FROM vi_EMSS WHERE Em_Dep = '+ @Id+' AND MonthYear = '''+@MonthYear+'''';
		EXEC (@sql)
		SELECT @TotalPage = [TotalRecord] FROM Temp
		IF (@TotalPage % @Record = 0) 
			SET @TotalPage = @TotalPage/@Record;
		ELSE 
			SET @TotalPage = @TotalPage/@Record + 1;
		DROP TABLE Temp;
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*
DECLARE @TotalPage int
EXEC sp_TotalPageSSInDep '08/2011', 3, 1, @TotalPage OUTPUT
PRINT @TotalPage
*/

IF OBJECT_ID ('sp_TotalPageSearch', 'p') IS NOT NULL
	DROP PROC sp_TotalPageSearch
Go
CREATE PROCEDURE sp_TotalPageSearch
	@ComId int,
	@Column varchar(100),
	@Search varchar(100),
	@TotalRecord int OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		DECLARE @sql varchar(500);
		DECLARE @Id varchar(3);
		SET @Id = CAST(@ComId AS varchar(3));
		SET @sql = 'SELECT COUNT(*) AS [TotalRecord] INTO Temp FROM dbo.vi_EMInfo 
					WHERE Dep_Com = '+@Id+' AND dbo.fn_ConvertVN_EN('+@Column+') LIKE N''%'+@Search+'%''';
		EXEC (@sql)
		SELECT @TotalRecord = [TotalRecord] FROM Temp
		DROP TABLE Temp;
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*
DECLARE @total int
EXEC sp_TotalPageSearch 2,'Em_FullName', 'n', @total OUTPUT
PRINT @total
*/

/*
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY singer_name) AS rownum, 
		* FROM singer WHERE singer_name like N'%"+Search+"%') AS listsinger 
			WHERE rownum >"+limit+"*("+page+"-1) AND rownum < ("+limit*page+"+1)
*/
-------------------------------------------------------------------------------------
IF OBJECT_ID ('sp_getTime', 'p') IS NOT NULL
	DROP PROC sp_getTime
Go
CREATE PROCEDURE sp_getTime
	@Datetime datetime OUTPUT
AS
BEGIN
	SELECT @Datetime = GETDATE()
END
GO
/*
DECLARE @Datetime datetime
EXEC sp_getTime @Datetime OUTPUT
PRINT @Datetime
*/